drop database QuanLyTour
SELECT @@MAX_Connections
use QuanLyTour
----------------------------------------------------Hop Dong----------------------------
CREATE PROCEDURE HopDong_load
AS
BEGIN
	SELECT HopDong.maHopDong, HopDong.maKhachHang, KhachHang.tenKhachHang, HopDong.maNhanVien, NhanVien.tenNhanVien, HopDong.maTour, Tour.tenTour, HopDong.ngayTao
	FROM HopDong, NhanVien, Tour, KhachHang
	WHERE HopDong.maKhachHang = KhachHang.maKhachHang
			and HopDong.maNhanVien = NhanVien.maNhanVien
			and HopDong.maTour = Tour.maTour
END
go

CREATE PROC HopDong_them(
@maKhachHang int,
@maNhanvien int,
@maTour int,
@ngayTao datetime
)
AS
BEGIN
	INSERT INTO HopDong( maKhachHang, maNhanvien, maTour, ngayTao) VALUES ( @maKhachHang, @maNhanvien, @maTour, @ngayTao) 
END
go

CREATE PROC HopDong_sua(
@maHopDong int,
@maKhachHang int,
@maNhanvien int,
@maTour int,
@ngayTao datetime 
)
AS
BEGIN
	UPDATE HopDong SET  maKhachHang = @maKhachHang, maNhanvien = @maNhanvien, maTour = @maTour, ngayTao = @ngayTao
	WHERE	maHopDong = @maHopDong
END

go
CREATE PROCEDURE HopDong_xoa
@maHopDong int
AS
BEGIN
	DELETE FROM HopDong WHERE maHopDong = @maHopDong
END

CREATE PROC HopDong_tim
@tenKhachHang char(50)
AS
BEGIN
	SELECT HopDong.maHopDong, HopDong.maKhachHang, KhachHang.tenKhachHang, HopDong.maNhanVien, NhanVien.tenNhanVien, HopDong.maTour, Tour.tenTour, HopDong.ngayTao
	FROM HopDong, NhanVien, Tour, KhachHang
	WHERE tenKhachHang LIKE '%'+ @tenKhachHang + '%'
			and HopDong.maKhachHang = KhachHang.maKhachHang
			and HopDong.maNhanVien = NhanVien.maNhanVien
			and HopDong.maTour = Tour.maTour
END
----------------------------------------------------Khach hang----------------------------
CREATE PROCEDURE KhachHang_load
AS
BEGIN
	SELECT * FROM KhachHang
END

go

CREATE PROC KhachHang_them(
@tenKhachHang char(50),
@ngaySinh datetime,
@gioiTinh bit,
@sdt nchar(11),
@email text,
@diaChi ntext 
)
AS
BEGIN
	INSERT INTO KhachHang( tenKhachHang, ngaySinh, gioiTinh, sdt, email, diaChi) VALUES ( @tenKhachHang, @ngaySinh, @gioiTinh, @sdt, @email, @diaChi) 
END
go

ALTER PROC KhachHang_sua(
@maKhachHang int,
@tenKhachHang nchar(50),
@ngaySinh datetime,
@gioiTinh bit,
@sdt nchar(11),
@email text,
@diaChi ntext 
)
AS
BEGIN
	UPDATE KhachHang SET  tenKhachHang = @tenKhachHang, ngaySinh = @ngaySinh, gioiTinh = @gioiTinh, sdt = @sdt, email = @email, diaChi = @diaChi
	WHERE	maKhachHang = @maKhachHang
END

go
CREATE PROCEDURE KhachHang_xoa
@maKhachHang int
AS
BEGIN
	DELETE FROM KhachHang WHERE maKhachHang = @maKhachHang
END

CREATE PROC KhachHang_tim
@tenKhachHang char(50)
AS
BEGIN
	SELECT * FROM KhachHang WHERE tenKhachHang LIKE '%' + @tenKhachHang + '%'
END
----------------------------------------------------Khach San----------------------------


go
CREATE PROCEDURE KhachSan_load
AS
BEGIN
	SELECT * FROM KhachSan
END

go
ALTER PROC KhachSan_them(
@tenKhachSan nchar(50),
@diaChi ntext,
@email text,
@sdt nchar(11),
@moTa ntext
)
AS
BEGIN
	INSERT INTO KhachSan( tenKhachSan, diaChi, email, sdt, moTa) VALUES ( @tenKhachSan, @diaChi, @email, @sdt, @moTa) 
END
go

CREATE PROC KhachSan_sua(
@maKhachSan int,
@tenKhachSan nchar(50),
@diaChi ntext,
@email text,
@sdt nchar(11),
@moTa ntext
)
AS
BEGIN
	UPDATE KhachSan SET  tenKhachSan = @tenKhachSan, diaChi = @diaChi, email = @email, sdt= @sdt, moTa = @moTa
	WHERE	maKhachSan = @maKhachSan
END

go
CREATE PROCEDURE KhachSan_xoa
@maKhachSan int
AS
BEGIN
	DELETE FROM KhachSan WHERE maKhachSan = @maKhachSan
END
go
CREATE PROC KhachSan_tim
@tenKhachSan nchar(50)
AS
BEGIN
	SELECT * FROM KhachSan WHERE tenKhachSan LIKE '%'+@tenKhachSan+'%'
END
----------------------------------------------------Nha Hang----------------------------
go
CREATE PROCEDURE NhaHang_load
AS
BEGIN
	SELECT * FROM NhaHang
END


go
CREATE PROC NhaHang_them(
@tenNhaHang nchar(50),
@diaChi ntext,
@email text,
@sdt nchar(11),
@moTa ntext
)
AS
BEGIN
	INSERT INTO NhaHang( tenNhaHang, diaChi, email, sdt, moTa) VALUES ( @tenNhaHang, @diaChi, @email, @sdt, @moTa) 
END
go

CREATE PROC NhaHang_sua(
@maNhaHang int,
@tenNhaHang nchar(50),
@diaChi ntext,
@email text,
@sdt nchar(11),
@moTa ntext
)
AS
BEGIN
	UPDATE NhaHang SET  tenNhaHang = @tenNhaHang, diaChi = @diaChi, email = @email, sdt= @sdt, moTa = @moTa
	WHERE	maNhaHang = @maNhaHang
END

go
CREATE PROCEDURE NhaHang_xoa
@maNhaHang int
AS
BEGIN
	DELETE FROM NhaHang WHERE maNhaHang = @maNhaHang
END

go
CREATE PROC NhaHang_tim
@tenNhaHang nchar(50)
AS
BEGIN
	SELECT * FROM NhaHang WHERE tenNhaHang LIKE '%' + @tenNhaHang + '%'
END

----------------------------------------------------Nhan vien----------------------------
go
CREATE PROCEDURE NhanVien_load
AS
BEGIN
	SELECT * FROM NhanVien
END

go
CREATE PROC NhanVien_them(
@tenNhanVien nchar(50),
@ngaySinh datetime,
@gioiTinh bit,
@sdt nchar(11),
@email text,
@diaChi ntext,
@chucVu ntext,
@matKhau char(50),
@quyen char(50)
)
AS
BEGIN
	INSERT INTO NhanVien( tenNhanVien, ngaySinh, gioiTinh, sdt, email, diaChi, chucVu, matKhau, quyen) VALUES ( @tenNhanVien, @ngaySinh, @gioiTinh, @sdt, @email, @diaChi, @chucVu, @matKhau, @quyen) 
END
go

CREATE PROC NhanVien_sua(
@maNhanVien int,
@tenNhanVien nchar(50),
@ngaySinh datetime,
@gioiTinh bit,
@sdt nchar(11),
@email text,
@diaChi ntext,
@chucVu ntext,
@matKhau char(50),
@quyen char(50) 
)
AS
BEGIN
	UPDATE NhanVien SET  tenNhanVien = @tenNhanVien, ngaySinh = @ngaySinh, gioiTinh = @gioiTinh, sdt = @sdt, email = @email, diaChi = @diaChi, chucVu = @chucVu, matKhau = @matKhau, quyen = @quyen
	WHERE	maNhanVien = @maNhanVien
END

go
CREATE PROCEDURE NhanVien_xoa
@maNhanVien int
AS
BEGIN
	DELETE FROM NhanVien WHERE maNhanVien = @maNhanVien
END

go
CREATE PROC NhanVien_tim
@tenNhanVien nchar(50)
AS
BEGIN
	SELECT * FROM NhanVien WHERE tenNhanVien LIKE '%' + @tenNhanVien + '%'
END
---------------------------------------------------------------Tour------------------------------------------------------------

go
CREATE PROCEDURE Tour_load
AS
BEGIN
		SELECT Tour.maTour, Tour.tenTour, Tour.noiKhoiHanh, Tour.moTa, Tour.loaiHinh, Tour.giaCa, DiaDanh.maDiaDanh, DiaDanh.tenDiaDanh, NhaHang.maNhaHang, NhaHang.tenNhaHang, KhachSan.maKhachSan, KhachSan.tenKhachSan, VanTai.maVanTai, VanTai.tenVanTai
FROM Tour, DiaDanh, ChiTiet_Tour_DiaDanh, NhaHang, ChiTiet_Tour_NhaHang, KhachSan, ChiTiet_Tour_KhachSan, VanTai, ChiTiet_Tour_VanTai
WHERE Tour.maTour = ChiTiet_Tour_DiaDanh.maTour and ChiTiet_Tour_DiaDanh.maDiaDanh = DiaDanh.maDiaDanh
	and Tour.maTour = ChiTiet_Tour_NhaHang.maTour and ChiTiet_Tour_NhaHang.maNhaHang = NhaHang.maNhaHang
	and Tour.maTour = ChiTiet_Tour_KhachSan.maTour and ChiTiet_Tour_KhachSan.maKhachSan = KhachSan.maKhachSan
	and Tour.maTour = ChiTiet_Tour_VanTai.maTour and ChiTiet_Tour_VanTai.maVanTai = VanTai.maVanTai
END
exec Tour_load
go


go
CREATE PROCEDURE Tour_load_only
AS
BEGIN
		SELECT * from Tour
END

go
ALTER PROC Tour_tim
@tenTour nchar(50)
AS
BEGIN
	SELECT Tour.maTour, Tour.tenTour, Tour.noiKhoiHanh, Tour.moTa, Tour.loaiHinh, Tour.giaCa, DiaDanh.maDiaDanh, DiaDanh.tenDiaDanh, NhaHang.maNhaHang, NhaHang.tenNhaHang, KhachSan.maKhachSan, KhachSan.tenKhachSan, VanTai.maVanTai, VanTai.tenVanTai
FROM Tour, DiaDanh, ChiTiet_Tour_DiaDanh, NhaHang, ChiTiet_Tour_NhaHang, KhachSan, ChiTiet_Tour_KhachSan, VanTai, ChiTiet_Tour_VanTai
WHERE Tour.tenTour LIKE '%' + @tenTour + '%'
	and Tour.maTour = ChiTiet_Tour_DiaDanh.maTour and ChiTiet_Tour_DiaDanh.maDiaDanh = DiaDanh.maDiaDanh
	and Tour.maTour = ChiTiet_Tour_NhaHang.maTour and ChiTiet_Tour_NhaHang.maNhaHang = NhaHang.maNhaHang
	and Tour.maTour = ChiTiet_Tour_KhachSan.maTour and ChiTiet_Tour_KhachSan.maKhachSan = KhachSan.maKhachSan
	and Tour.maTour = ChiTiet_Tour_VanTai.maTour and ChiTiet_Tour_VanTai.maVanTai = VanTai.maVanTai
END

go
CREATE PROC Tour_them(
@maTour char(50),
@maDiaDanh int,
@maNhaHang int,
@maVanTai int,
@maKhachSan int,
@tenTour nchar(50),
@noiKhoiHanh ntext,
@giaCa money,
@moTa ntext,
@loaiHinh ntext
)
AS
BEGIN
	INSERT INTO Tour(maTour, tenTour, noiKhoiHanh, giaCa, moTa, loaiHinh) VALUES (@maTour, @tenTour, @noiKhoiHanh, @giaCa, @moTa, @loaiHinh ) 
	INSERT INTO ChiTiet_Tour_DiaDanh(maTour, maDiaDanh) VALUES (@maTour,@maDiaDanh)
	INSERT INTO ChiTiet_Tour_NhaHang(maTour, maNhaHang) VALUES (@maTour,@maNhaHang)
	INSERT INTO ChiTiet_Tour_KhachSan(maTour, maKhachSan) VALUES (@maTour,@maKhachSan)
	INSERT INTO ChiTiet_Tour_VanTai(maTour, maVanTai) VALUES (@maTour,@maVanTai)
END
go
Tour_them to02,1,1,1,1,'kaka','kaka',1000,'kaka','du lich trong nuoc'

CREATE PROC Tour_sua(
@maTour char(50),
@maDiaDanh int,
@maNhaHang int,
@maVanTai int,
@maKhachSan int,
@tenTour nchar(50),
@noiKhoiHanh ntext,
@giaCa money,
@moTa ntext,
@loaiHinh ntext
)
AS
BEGIN
	UPDATE Tour SET  tenTour = @tenTour, noiKhoiHanh = @noiKhoiHanh, giaCa = @giaCa, moTa = @moTa, loaiHinh = @loaiHinh
	WHERE	maTour = @maTour
	UPDATE ChiTiet_Tour_DiaDanh SET  maDiaDanh = @maDiaDanh
	WHERE	maTour = @maTour
	UPDATE ChiTiet_Tour_NhaHang SET  maNhaHang = @maNhaHang
	WHERE	maTour = @maTour
	UPDATE ChiTiet_Tour_KhachSan SET  maKhachSan = @maKhachSan
	WHERE	maTour = @maTour
	UPDATE ChiTiet_Tour_VanTai SET  maVanTai = @maVanTai
	WHERE	maTour = @maTour
END

go
CREATE PROCEDURE Tour_xoa
@maTour char(50)
AS
BEGIN
	DELETE FROM ChiTiet_Tour_DiaDanh WHERE maTour = @maTour 
	DELETE FROM ChiTiet_Tour_KhachSan WHERE maTour = @maTour
	DELETE FROM ChiTiet_Tour_NhaHang WHERE maTour = @maTour
	DELETE FROM ChiTiet_Tour_VanTai WHERE maTour = @maTour
	DELETE FROM Tour WHERE maTour = @maTour
END
------------------------------------------------------------------------------------------------------------------
---------------------------------------------------VE-------------------------------------------------------------

go
CREATE PROCEDURE Ve_load
AS
BEGIN
	SELECT Ve.maVe, Ve.maHopDong, KhachHang.tenKhachHang, Tour.tenTour
	FROM Ve , KhachHang, HopDong, Tour
	WHERE Ve.maHopDong = HopDong.maHopDong
		and HopDong.maKhachHang = KhachHang.maKhachHang
		and HopDong.maTour = Tour.maTour
END

go
CREATE PROC Ve_them(
@maHopDong int 
)
AS
BEGIN
	INSERT INTO Ve( maHopDong) VALUES ( @maHopDong) 
END
go

CREATE PROC Ve_sua(
@maVe int,
@maHopDong int  
)
AS
BEGIN
	UPDATE Ve SET maHopDong = @maHopDong
	WHERE	maVe = @maVe
END

go
CREATE PROCEDURE Ve_xoa
@maVe int
AS
BEGIN
	DELETE FROM Ve WHERE maVe = @maVe
END

go
CREATE PROCEDURE Ve_tim
@tenKhachHang nchar(50)
AS
BEGIN
	SELECT Ve.maVe, Ve.maHopDong
	FROM Ve , KhachHang, HopDong
	WHERE tenKhachHang LIKE '%' + @tenKhachHang + '%'
		and Ve.maHopDong = HopDong.maHopDong
		and HopDong.maKhachHang = KhachHang.maKhachHang
END
---------------------------------------------------DiaDanh------------------------------------------------------------

go
CREATE PROCEDURE DiaDanh_load
AS
BEGIN
	SELECT * FROM DiaDanh
END

go
CREATE PROC DiaDanh_tim
@tenDiaDanh nchar(50)
AS
BEGIN
	SELECT * FROM DiaDanh WHERE tenDiaDanh = '%' + @tenDiaDanh + '%'
END

go
ALTER PROC DiaDanh_them(
@tenDiaDanh Nchar(50),
@moTa ntext,
@diaChi ntext 
)
AS
BEGIN
	INSERT INTO DiaDanh (tenDiaDanh, moTa, diaChi) VALUES (@tenDiaDanh, @moTa, @diaChi) 
END
go
DiaDanh_them khanh, khanh, khanh
go

CREATE PROC DiaDanh_sua(
@maDiaDanh int,
@tenDiaDanh Nchar(50),
@moTa ntext,
@diaChi ntext 
)
AS
BEGIN
	UPDATE DiaDanh SET  tenDiaDanh = @tenDiaDanh, moTa = @moTa, diaChi = @diaChi
	WHERE	maDiaDanh = @maDiaDanh
END

go
CREATE PROCEDURE DiaDanh_xoa
@maDiaDanh int
AS
BEGIN
	DELETE FROM DiaDanh WHERE maDiaDanh LIKE @maDiaDanh
END
go

---------------------------------------------------VAN TAI-------------------------------------------------------------
go
CREATE PROCEDURE VanTai_load
AS
BEGIN
	SELECT * FROM VanTai
END


go
CREATE PROC VanTai_them(
@tenVanTai Nchar(50),
@loaiHinh ntext,
@moTa ntext,
@sdt nchar(11),
@email text
)
AS
BEGIN
	INSERT INTO VanTai( tenVanTai, loaiHinh, moTa, sdt, email) VALUES ( @tenVanTai, @loaiHinh, @moTa, @sdt, @email) 
END
go

CREATE PROC VanTai_sua(
@maVanTai int,
@tenVanTai Nchar(50),
@loaiHinh ntext,
@moTa ntext,
@sdt nchar(11),
@email text
)
AS
BEGIN
	UPDATE VanTai SET  tenVanTai = @tenVanTai, loaiHinh = @loaiHinh, moTa = @moTa, sdt= @sdt, email = @email
	WHERE	maVanTai = @maVanTai
END

go
CREATE PROCEDURE VanTai_xoa
@maVanTai int
AS
BEGIN
	DELETE FROM VanTai WHERE maVanTai = @maVanTai
END

go
CREATE PROC VanTai_tim
@tenVanTai nchar(50)
AS
BEGIN
	SELECT * FROM VanTai WHERE tenVantai LIKE '%' + @tenVanTai + '%'
END
-------------------------------------------DangNhap---------------------------------

create PROC dangNhap
(
@maNhanVien int,
@matKhau char(50)
) 
AS
BEGIN
	SELECT * FROM NhanVien WHERE maNhanVien = @maNhanVien and matkhau = @matKhau
END